package com.epalmpay.service.yunstream.datatransfer.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class DbHelper {

	static Connection conn = null;
	public String password;
	public String user;
	public String IP;
	public String port;
	public String dbName;
	public String operPassword;

	//是否测试状态
	public static boolean  isTest = false;

    private static DbHelper myDbHelper;

    boolean  isJar=true;
	private DbHelper() {
		super();
		
		this.password = "zhanGfu613coMm";
		this.user = "root";
		//this.IP = "121.40.224.25";
		this.IP = "121.40.224.25";
		this.port = "3306";
		this.dbName = "pay_app";
		this.operPassword = "zhanGfu613coMm";
		
//		this.password = "12345678";
//		this.user = "root";
//		this.IP = "localhost";
//		this.port = "3306";
//		this.dbName = "test";
		
//		this.password = "123456";
//		this.user = "app";
//		this.IP = "202.102.89.74";
//		this.port = "3306";
//		this.dbName = "app";
	}
	
	public static DbHelper getDBHelper(){
		if(myDbHelper==null){
			myDbHelper=new DbHelper();
			//myDbHelper.loadProperty();
		}
		return myDbHelper;
	}

	public boolean openDatabase() {
		if (password != null && user != null && IP != null && port != null) {
			if (conn == null) {
				String url = "jdbc:mysql://" + IP + ":" + port + "/"+dbName
						+ "?user=" + user + "&password=" + password
						+ "&useUnicode=true&characterEncoding=UTF8";
				System.out.println("url..."+url);
				try {
					Class.forName("com.mysql.jdbc.Driver");
					conn = DriverManager.getConnection(url);
				} catch (ClassNotFoundException e) {
					e.printStackTrace();
					return false;
				} catch (SQLException e) {
					e.printStackTrace();
					return false;
				}
				return true;
			} else {
				return true;
			}
		} else {
			return false;
		}

	}

	public boolean closeDataBase() {
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		conn = null;
		return true;
	}

	public List<Map<String, String>> findRechargeOrder() {
	
	  Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> orders=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT order_id, order_msg_txt FROM pay_order WHERE order_msg_txt LIKE '%外部充值ֵ%'";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("order_id", rs.getString("order_id"));
				map.put("order_msg_txt", rs.getString("order_msg_txt"));
				orders.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return orders;
	}

	public void updateRelateOrderId(Integer orderId, int relateOrderId) {
		
		Statement stmt = null;
			
		try {
			stmt = conn.createStatement();
			String sql="UPDATE pay_order SET relate_order_id="+relateOrderId+" WHERE  order_id="+orderId.intValue();
			System.out.println("updateRelateOrderId....."+sql);
			stmt.executeUpdate(sql);
			
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	public int findRelateOrderId(String relationOrderNum) {
		Statement stmt = null;
		ResultSet rs = null;		
		try {
			stmt = conn.createStatement();
			String sql="SELECT recharge_order_id FROM pay_order_recharge WHERE  recharge_order_num ='"+relationOrderNum+"'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				return rs.getInt("recharge_order_id");
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return 0;
	}

	public ArrayList<Map<String, String>> findAllOilCard() {
		
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> orders=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT * FROM yl_oilcard_order";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {					
				Map<String, String> map=new HashMap<String, String>();
				map.put("id", rs.getString("id"));
				map.put("oilcard_order_id", rs.getString("oilcard_order_id"));
				map.put("payment_order_id", rs.getString("payment_order_id"));
				map.put("oilcard_recharge_order_id", rs.getString("oilcard_recharge_order_id"));
				map.put("payment_status", rs.getString("payment_status"));
				map.put("recharge_status", rs.getString("recharge_status"));
				map.put("recharge_amount", rs.getString("recharge_amount"));
				map.put("oilcard_recharge_result_cord", rs.getString("oilcard_recharge_result_cord"));
				
				map.put("recharge_req_time", rs.getString("recharge_req_time"));
				map.put("recharge_time", rs.getString("recharge_time"));
				
				map.put("recharge_amount", rs.getString("recharge_amount"));
				map.put("added_value_status", rs.getString("added_value_status"));
				map.put("replenish_id", rs.getString("replenish_id"));
				
				orders.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return orders;
	}

	public void updateOilCard(Map<String, String> oilOrder) {
		 
		  Statement stmt = null;
			
			try {
				stmt = conn.createStatement();
				StringBuffer sql=new StringBuffer("UPDATE yl_oilcard_order SET oilcard_ordernum=");
				sql.append("'").append(oilOrder.get("oilcard_recharge_order_id")).append("',");
				sql.append("order_package_type=0,").append("oilcard_package_id=");
				if(oilOrder.get("oilcard_recharge_order_id").startsWith("ZSH")){
					sql.append("22,");
				}else{
					sql.append("26,");
				}
				if("2".equals(oilOrder.get("payment_status"))){
					if("3".equals(oilOrder.get("recharge_status"))){
						sql.append("order_status=3");
					}else{
						sql.append("order_status=5");
					}
				}else{
					sql.append("order_status=0");
				}
				sql.append(" WHERE  id=").append(oilOrder.get("id"));
				System.out.println("updateOilCard....."+sql);
				stmt.executeUpdate(sql.toString());
				
			} catch (SQLException e) {
				e.printStackTrace();
				closeDataBase();
			} finally {
				if (stmt != null) {
					try {
						stmt.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
	}

	public void insertChargeOrder(Map<String, String> oilOrder) {

		Statement stmt = null;
		ResultSet rs = null;
		
		try {
			stmt = conn.createStatement();
			String sql = "SELECT id FROM yl_oilcard_recharge_info WHERE  oilcard_order_id ="
					+ oilOrder.get("id");
			rs = stmt.executeQuery(sql);
			String rechargeReqTime = oilOrder.get("recharge_req_time");
			String rechargeTime =oilOrder.get("recharge_time");
			if(rechargeTime==null||rechargeTime.trim().length()==0){
				rechargeTime=rechargeReqTime;
			}
			String oilcardRechargeResultCord = oilOrder.get("oilcard_recharge_result_cord");
			String addedValueStatus = oilOrder.get("added_value_status");
			String rechargeAmount = oilOrder.get("recharge_amount");
			String rechargeStatus = oilOrder.get("recharge_status");
			String replenishId = oilOrder.get("replenish_id");
			String oilcardOrderNum = oilOrder.get("oilcard_order_id");
			if(oilcardOrderNum==null||oilcardOrderNum.trim().length()==0){
				oilcardOrderNum="";
			}
			String oilcardRechargeOrderNum =  oilOrder.get("oilcard_recharge_order_id");
			String oilcardOrderId = oilOrder.get("id");
			StringBuffer sqlBuffer = null;
			if (rs.next()) {
				String id = rs.getString("id");

				stmt.close();
				stmt = conn.createStatement();

				sqlBuffer = new StringBuffer(
						"update yl_oilcard_recharge_info set recharge_req_time ='")
						.append(rechargeReqTime)
						.append("',oilcard_recharge_result_cord='")
						.append(oilcardRechargeResultCord)
						.append("',recharge_status='").append(rechargeStatus)
						.append("',recharge_time='").append(rechargeTime)
						.append("',added_value_status='")
						.append(addedValueStatus).append("',replenish_id=")
						.append(replenishId).append(",recharge_amount='")
						.append(rechargeAmount)
						.append("',oilcard_recharge_order_num='")
						.append(oilcardRechargeOrderNum)
						.append("',oilcard_order_num='")
						.append(oilcardOrderNum).append("',oilcard_order_id='")
						.append(oilcardOrderId).append("'  where id = ")
						.append(id);
			} else {
				stmt.close();
				stmt = conn.createStatement();

				sqlBuffer = new StringBuffer(
						"insert into yl_oilcard_recharge_info (recharge_req_time, oilcard_recharge_result_cord, ")
						.append("recharge_status, recharge_time, added_value_status, recharge_amount,")
						.append("replenish_id, oilcard_recharge_order_num, ")
						.append("oilcard_order_num, oilcard_order_id)values ('")
						.append(rechargeReqTime).append("','")
						.append(oilcardRechargeResultCord + "','")
						.append(rechargeStatus).append("','")
						.append(rechargeTime).append("','")
						.append(addedValueStatus).append("','")
						.append(rechargeAmount).append("',").append(replenishId)
						.append(",'").append(oilcardRechargeOrderNum)
						.append("','").append(oilcardOrderNum).append("','")
						.append(oilcardOrderId).append("')");
			}
			System.out.println("insertChargeOrder��������" + sqlBuffer.toString());
			stmt.execute(sqlBuffer.toString());
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}

	}

	public void updateYlOilcardReplenishOrder(Map<String, String> oilOrder) {
		
		Statement stmt = null;
		ResultSet rs = null;		
		try {
			stmt = conn.createStatement();
			String sql="SELECT id FROM yl_oilcard_recharge_info WHERE  oilcard_order_id ='"+ oilOrder.get("id")+"'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				int oilcardRechargeInfoId= rs.getInt("id");
				stmt.close();
				stmt = conn.createStatement();
				String sqlBuffer="update yl_oilcard_replenish_order set oilcard_recharge_id="+oilcardRechargeInfoId+" where oilcard_order_id="+oilOrder.get("id");
				System.out.println("updateYlOilcardReplenishOrder��������" + sqlBuffer);
				stmt.execute(sqlBuffer);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	////////////////////////////////////////////////////////////////
	///获取会员信息
	public List<Map<String, String>> findAllMember() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> members=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT * FROM pay_member where (member_role_id = 6 or member_role_id = 10) " +
					(isTest?"and member_id = 253":"");

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("member_id", rs.getString("member_id"));
				map.put("member_virtual_card", rs.getString("member_virtual_card"));
				map.put("member_role_id", rs.getString("member_role_id"));
				map.put("member_customer_name", rs.getString("member_customer_name"));
				map.put("member_id_number", rs.getString("member_id_number"));
				map.put("member_pay_pwd", rs.getString("member_pay_pwd"));
				map.put("member_login_pwd", rs.getString("member_login_pwd"));
				map.put("member_mobile_phone", rs.getString("member_mobile_phone"));
				map.put("member_sex", rs.getString("member_sex"));
				map.put("member_email", rs.getString("member_email"));
				map.put("member_openid", rs.getString("member_openid"));
				map.put("member_regtime", rs.getString("member_regtime"));
				map.put("member_balance_b", rs.getString("member_balance_b"));
				map.put("member_balance", rs.getString("member_balance"));
				map.put("member_verify_status", rs.getString("member_verify_status"));
				map.put("member_accounts_status", rs.getString("member_accounts_status"));
				map.put("member_status", rs.getString("member_status"));
				map.put("system_userid", rs.getString("system_userid"));
				map.put("img_url", rs.getString("img_url"));
				members.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return members;
	}

	///获取会员银行卡信息
	public List<Map<String, String>> findMemberCardInfo(long memberId) {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> members=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT * FROM pay_member_card where tx_card_member_id =" + memberId;
			System.out.println("调用方法名findMemberCardInfo的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("tx_card_org_order_num", rs.getString("tx_card_org_order_num"));
				map.put("tx_card_num", rs.getString("tx_card_num"));
				map.put("tx_card_account_type", rs.getString("tx_card_account_type"));
				map.put("tx_card_issuer_id", rs.getString("tx_card_issuer_id"));
				map.put("tx_card_integral", rs.getString("tx_card_integral"));
				map.put("tx_card_info", rs.getString("tx_card_info"));
				map.put("tx_card_desc", rs.getString("tx_card_desc"));
				map.put("tx_card_acc_name", rs.getString("tx_card_acc_name"));
				map.put("tx_card_no", rs.getString("tx_card_no"));
				map.put("tx_card_mobile", rs.getString("tx_card_mobile"));
				map.put("tx_card_type", rs.getString("tx_card_type"));
				map.put("tx_card_status", rs.getString("tx_card_status"));
				map.put("tx_card_cvn", rs.getString("tx_card_cvn"));
				map.put("tx_card_expdate", rs.getString("tx_card_expdate"));
				map.put("tx_card_account_type_value", rs.getString("tx_card_account_type_value"));
				members.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return members;
	}

	///获取会员地址信息
	public List<Map<String, String>> findMemberAddress() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> addressList=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT pa.*,pm.member_mobile_phone " +
					"FROM pay_address pa left JOIN pay_member pm on pa.address_member_id = pm.member_id " +
					(isTest?"where address_id = 123":"");
			System.out.println("调用方法名findMemberAddress的sql："+sql);

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("address_id", rs.getString("address_id"));
				map.put("address_member_id", rs.getString("address_member_id"));
				map.put("address_customer_name", rs.getString("address_customer_name"));
				map.put("address_mobile_phone", rs.getString("address_mobile_phone"));
				map.put("address_city", rs.getString("address_city"));
				map.put("address_area", rs.getString("address_area"));
				map.put("default_select", rs.getString("default_select"));
				map.put("status", rs.getString("status"));
				map.put("member_mobile_phone", rs.getString("member_mobile_phone"));
				addressList.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return addressList;
	}

	///获取会员油卡信息
	public List<Map<String, String>> findMemberOilCard() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> addressList=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT mo.*,pm.member_mobile_phone " +
					"FROM yl_member_oilcard mo left JOIN pay_member pm on mo.member_id = pm.member_openid " +
					(isTest?"where mo.id = 19":"");
			System.out.println("调用方法名 findMemberOilCard 的sql："+sql);

			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("member_id", rs.getString("member_id"));
				map.put("oilcard_type", rs.getString("oilcard_type"));
				map.put("oilcard_number", rs.getString("oilcard_number"));
				map.put("oilcard_owen", rs.getString("oilcard_owen"));
				map.put("oilcard_phone", rs.getString("oilcard_phone"));
				map.put("create_time", rs.getString("create_time"));
				map.put("update_time", rs.getString("update_time"));
				map.put("isdefault", rs.getString("isdefault"));
				map.put("member_mobile_phone", rs.getString("member_mobile_phone"));
				addressList.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return addressList;
	}

	///获取商品订单信息
	public List<Map<String, String>> findAllGoodOrders() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> members=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT pgo.*,pm.member_mobile_phone " +
					"FROM pay_goods_order pgo left JOIN pay_member pm on pgo.goods_order_member_id = pm.member_id " +
					(isTest?"where goods_order_id = 1":"");
			System.out.println("调用方法名 findAllGoodOrders 的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("goods_order_id", rs.getString("goods_order_id"));
				map.put("goods_order_member_id", rs.getString("goods_order_member_id"));
				map.put("goods_order_code", rs.getString("goods_order_code"));
				map.put("goods_order_number", rs.getString("goods_order_number"));
				map.put("goods_order_price", rs.getString("goods_order_price"));
				map.put("goods_order_datetime", rs.getString("goods_order_datetime"));
				map.put("goods_order_fk_datetime", rs.getString("goods_order_fk_datetime"));
				map.put("goods_order_fh_datetime", rs.getString("goods_order_fh_datetime"));
				map.put("goods_order_fh_name", rs.getString("goods_order_fh_name"));
				map.put("goods_order_fh_code", rs.getString("goods_order_fh_code"));
				map.put("goods_order_address_customer_name", rs.getString("goods_order_address_customer_name"));
				map.put("goods_order_address_mobile_phone", rs.getString("goods_order_address_mobile_phone"));
				map.put("goods_order_address_city", rs.getString("goods_order_address_city"));
				map.put("goods_order_address_area", rs.getString("goods_order_address_area"));
				map.put("goods_order_complete_datetime", rs.getString("goods_order_complete_datetime"));
				map.put("goods_order_qx_datetime", rs.getString("goods_order_qx_datetime"));
				map.put("goods_order_status", rs.getString("goods_order_status"));
				map.put("delete_status", rs.getString("delete_status"));
				map.put("member_mobile_phone", rs.getString("member_mobile_phone"));
				members.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return members;
	}

	///获取商品订单明细
	public List<Map<String, String>> getGoodOrderItem(long orderId) {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items=new ArrayList<Map<String, String>>();
		try {
			stmt = conn.createStatement();
			String sql="SELECT * FROM pay_goods_order_items where item_goods_order_id =" + orderId;
			System.out.println("调用方法名getGoodOrderItem的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map=new HashMap<String, String>();
				map.put("item_id", rs.getString("item_id"));
				map.put("item_goods_order_id", rs.getString("item_goods_order_id"));
				map.put("item_goods_id", rs.getString("item_goods_id"));
				map.put("item_goods_name", rs.getString("item_goods_name"));
				map.put("item_goods_image", rs.getString("item_goods_image"));
				map.put("item_goods_original_price", rs.getString("item_goods_original_price"));
				map.put("item_goods_promotion_price", rs.getString("item_goods_promotion_price"));
				map.put("item_goods_number", rs.getString("item_goods_number"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	///获取商品订单交易记录
	public Map<String, String> getGoodOrderTrade(long orderId) {

		Statement stmt = null;
		ResultSet rs = null;
		//ArrayList<Map<String, String>> items=new ArrayList<Map<String, String>>();
		Map<String, String> map = null;
		try {
			stmt = conn.createStatement();
			String sql = "SELECT pogp.*,po.* FROM pay_order_goods_pay pogp left JOIN pay_order po on pogp.goods_pay_order_id = po.order_id "
					       + "where pogp.goods_pay_status = 1 and pogp.goods_pay_goods_order_id =" + orderId;
			System.out.println("调用方法名getGoodOrderTrade的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				map = new HashMap<String, String>();
				map.put("goods_pay_id", rs.getString("goods_pay_id"));
				map.put("goods_pay_order_id", rs.getString("goods_pay_order_id"));
				map.put("goods_pay_goods_order_id", rs.getString("goods_pay_goods_order_id"));
				map.put("goods_pay_order_time", rs.getString("goods_pay_order_time"));
				map.put("goods_pay_price", rs.getString("goods_pay_price"));
				map.put("goods_pay_status", rs.getString("goods_pay_status"));
				map.put("goods_pay_type", rs.getString("goods_pay_type"));
				map.put("goods_pay_ordernum", rs.getString("goods_pay_ordernum"));
				//////////////////////////////////
				map.put("order_id", rs.getString("order_id"));
				map.put("order_type", rs.getString("order_type"));
				map.put("order_userid", rs.getString("order_userid"));
				map.put("order_groupid", rs.getString("order_groupid"));
				map.put("order_merchantid", rs.getString("order_merchantid"));
				map.put("order_orgtrandatetime", rs.getString("order_orgtrandatetime"));
				map.put("order_systrace_num", rs.getString("order_systrace_num"));
				map.put("order_resp_code", rs.getString("order_resp_code"));
				map.put("order_msg_txt", rs.getString("order_msg_txt"));
				map.put("order_datetime", rs.getString("order_datetime"));
				map.put("relate_order_id", rs.getString("relate_order_id"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return map;
	}

	///获取转账交易记录
	public List<Map<String, String>> getAllTransferTrade() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT pot.*,po.*," +
					"(select member_mobile_phone from pay_member where member_userid = pot.transfer_to_userid) transfer_userid_phone," +
					"(select member_mobile_phone from pay_member where member_userid = po.order_userid) order_userid_phone" +
					" FROM pay_order_transfer pot inner JOIN pay_order po on pot.transfer_order_id = po.order_id "
					+ "where po.order_type =2 and pot.transfer_status = 1 "
					+ (isTest?"and pot.transfer_id = 90":"");
			System.out.println("调用方法名getAllTransferTrade的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("transfer_id", rs.getString("transfer_id"));
				map.put("transfer_order_id", rs.getString("transfer_order_id"));
				map.put("transfer_order_num", rs.getString("transfer_order_num"));
				map.put("transfer_order_time", rs.getString("transfer_order_time"));
				map.put("transfer_tran_amt", rs.getString("transfer_tran_amt"));
				map.put("transfer_order_currency", rs.getString("transfer_order_currency"));
				map.put("transfer_to_userid", rs.getString("transfer_to_userid"));
				map.put("transfer_userid_phone", rs.getString("transfer_userid_phone"));
				map.put("transfer_to_username", rs.getString("transfer_to_username"));
				map.put("transfer_status", rs.getString("transfer_status"));
				map.put("transfer_outer_orderid", rs.getString("transfer_outer_orderid"));
				map.put("transfer_handlingcharge", rs.getString("transfer_handlingcharge"));
				//////////////////////////////////
				map.put("order_id", rs.getString("order_id"));
				map.put("order_type", rs.getString("order_type"));
				map.put("order_userid", rs.getString("order_userid"));
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				map.put("order_groupid", rs.getString("order_groupid"));
				map.put("order_merchantid", rs.getString("order_merchantid"));
				map.put("order_orgtrandatetime", rs.getString("order_orgtrandatetime"));
				map.put("order_systrace_num", rs.getString("order_systrace_num"));
				map.put("order_resp_code", rs.getString("order_resp_code"));
				map.put("order_msg_txt", rs.getString("order_msg_txt"));
				map.put("order_datetime", rs.getString("order_datetime"));
				map.put("relate_order_id", rs.getString("relate_order_id"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	///获取充值交易记录
	public List<Map<String, String>> getAllRechargeTrade() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT por.*," +
					"po.*,(select member_mobile_phone from pay_member where member_userid = po.order_userid) order_userid_phone" +
					" FROM pay_order_recharge por inner JOIN pay_order po on por.recharge_order_id = po.order_id "
					+ "where por.recharge_status = 3 " + (isTest?"and por.recharge_id = 235":"");
			System.out.println("调用方法名getAllRechargeTrade的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("recharge_id", rs.getString("recharge_id"));
				map.put("recharge_order_id", rs.getString("recharge_order_id"));
				map.put("recharge_order_num", rs.getString("recharge_order_num"));
				map.put("recharge_order_time", rs.getString("recharge_order_time"));
				map.put("recharge_tran_amt", rs.getString("recharge_tran_amt"));
				map.put("recharge_type", rs.getString("recharge_type"));
				map.put("recharge_bank_name", rs.getString("recharge_bank_name"));
				map.put("recharge_card_id", rs.getString("recharge_card_id"));
				map.put("recharge_qrcode", rs.getString("recharge_qrcode"));
				map.put("recharge_status", rs.getString("recharge_status"));
				map.put("recharge_pay_ordernum", rs.getString("recharge_pay_ordernum"));
				//////////////////////////////////
				map.put("order_id", rs.getString("order_id"));
				map.put("order_type", rs.getString("order_type"));
				map.put("order_userid", rs.getString("order_userid"));
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				map.put("order_groupid", rs.getString("order_groupid"));
				map.put("order_merchantid", rs.getString("order_merchantid"));
				map.put("order_orgtrandatetime", rs.getString("order_orgtrandatetime"));
				map.put("order_systrace_num", rs.getString("order_systrace_num"));
				map.put("order_resp_code", rs.getString("order_resp_code"));
				map.put("order_msg_txt", rs.getString("order_msg_txt"));
				map.put("order_datetime", rs.getString("order_datetime"));
				map.put("relate_order_id", rs.getString("relate_order_id"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	///获取提现交易记录
	public List<Map<String, String>> getAllWithdrawalTrade() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT paw.*," +
					"po.*,(select member_mobile_phone from pay_member where member_userid = po.order_userid) order_userid_phone" +
					" FROM pay_account_withdrawal paw left JOIN pay_order po on paw.withdrawal_order_id = po.order_id "
					+ "where paw.withdrawal_status = 3 " + (isTest?"and paw.withdrawal_id = 168":"");
			System.out.println("调用方法名getAllWithdrawalTrade的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("withdrawal_id", rs.getString("withdrawal_id"));
				map.put("withdrawal_order_id", rs.getString("withdrawal_order_id"));
				map.put("withdrawal_order_num", rs.getString("withdrawal_order_num"));
				map.put("withdrawal_order_time", rs.getString("withdrawal_order_time"));
				map.put("withdrawal_tran_amt", rs.getString("withdrawal_tran_amt"));
				map.put("withdrawal_to_info", rs.getString("withdrawal_to_info"));
				map.put("withdrawal_to_time", rs.getString("withdrawal_to_time"));
				map.put("withdrawal_order_status", rs.getString("withdrawal_order_status"));
				map.put("withdrawal_status", rs.getString("withdrawal_status"));
				map.put("withdrawal_fee_amt", rs.getString("withdrawal_fee_amt"));
				map.put("sys_orderid", rs.getString("sys_orderid"));
				//////////////////////////////////
				map.put("order_id", rs.getString("order_id"));
				map.put("order_type", rs.getString("order_type"));
				map.put("order_userid", rs.getString("order_userid"));
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				map.put("order_groupid", rs.getString("order_groupid"));
				map.put("order_merchantid", rs.getString("order_merchantid"));
				map.put("order_orgtrandatetime", rs.getString("order_orgtrandatetime"));
				map.put("order_systrace_num", rs.getString("order_systrace_num"));
				map.put("order_resp_code", rs.getString("order_resp_code"));
				map.put("order_msg_txt", rs.getString("order_msg_txt"));
				map.put("order_datetime", rs.getString("order_datetime"));
				map.put("relate_order_id", rs.getString("relate_order_id"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	///获取油卡充值交易记录
	public List<Map<String, String>> getAllOilCardRechargeTrade() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT yoo.*," +
					"(select member_mobile_phone from pay_member where member_openid = yoo.member_id) order_userid_phone" +
					" FROM yl_oilcard_order yoo "
					+ (isTest?"where yoo.id = 321":"");
			System.out.println("调用方法名getAllRechargeTrade的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("payment_order_id", rs.getString("payment_order_id"));
				map.put("member_id", rs.getString("member_id"));
				map.put("oilcard_number", rs.getString("oilcard_number"));
				map.put("payment_type", rs.getString("payment_type"));
				map.put("payment_status", rs.getString("payment_status"));
				map.put("payment_amount", rs.getString("payment_amount"));
				map.put("recharge_amount", rs.getString("recharge_amount"));
				map.put("oilcard_owner", rs.getString("oilcard_owner"));
				map.put("oilcard_type", rs.getString("oilcard_type"));
				map.put("oilcard_phone", rs.getString("oilcard_phone"));
				map.put("payment_result_cord", rs.getString("payment_result_cord"));
				map.put("payment_time", rs.getString("payment_time"));
				map.put("order_create_time", rs.getString("order_create_time"));
				map.put("pay_order_id", rs.getString("pay_order_id"));
				map.put("oilcard_ordernum", rs.getString("oilcard_ordernum"));
				map.put("order_package_type", rs.getString("order_package_type"));
				map.put("oilcard_package_id", rs.getString("oilcard_package_id"));
				map.put("order_status", rs.getString("order_status"));
				map.put("id", rs.getString("id"));
				//////////////////////////////////
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	///获取流量卡充值交易记录
	public List<Map<String, String>> getAllFlowRechargeTrade() {

		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT c.*," +
					"o.order_type,o.order_userid,o.order_groupid,o.order_merchantid,o.order_orgtrandatetime,o.order_systrace_num," +
					"o.order_resp_code,o.order_msg_txt,o.order_datetime,o.relate_order_id," +
					"(select member_mobile_phone from pay_member where member_userid = o.order_userid) order_userid_phone" +
					" from pay_order_flow_charge c inner join pay_order o on o.order_id = c.order_id "
					+ (isTest?"where c.id = 45":"");
			System.out.println("调用方法名getAllRechargeTrade的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("pay_order_num", rs.getString("pay_order_num"));
				map.put("sys_order_number", rs.getString("sys_order_number"));
				map.put("order_number", rs.getString("order_number"));
				map.put("pay_date", rs.getString("pay_date"));
				map.put("order_id", rs.getString("order_id"));
				map.put("pay_amount", rs.getString("pay_amount"));
				map.put("pay_type", rs.getString("pay_type"));
				map.put("notify_respond", rs.getString("notify_respond"));
				map.put("notify_url", rs.getString("notify_url"));
				map.put("pay_status", rs.getString("pay_status"));
				//////////////////////////////////
				//map.put("order_id", rs.getString("order_id"));
				map.put("order_type", rs.getString("order_type"));
				map.put("order_userid", rs.getString("order_userid"));
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				map.put("order_groupid", rs.getString("order_groupid"));
				map.put("order_merchantid", rs.getString("order_merchantid"));
				map.put("order_orgtrandatetime", rs.getString("order_orgtrandatetime"));
				map.put("order_systrace_num", rs.getString("order_systrace_num"));
				map.put("order_resp_code", rs.getString("order_resp_code"));
				map.put("order_msg_txt", rs.getString("order_msg_txt"));
				map.put("order_datetime", rs.getString("order_datetime"));
				map.put("relate_order_id", rs.getString("relate_order_id"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	public List<Map<String, String>> getAllOilCardRechargeInfo(String oilOrderId) {
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT yoo.* FROM yl_oilcard_recharge_info yoo  where oilcard_order_id=" +oilOrderId;
					
			System.out.println("调用方法名getAllOilCardRechargeInfo的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("id", rs.getString("id"));
				 
				map.put("recharge_req_time", rs.getString("recharge_req_time"));
				map.put("oilcard_recharge_result_cord", rs.getString("oilcard_recharge_result_cord"));
				map.put("recharge_amount", rs.getString("recharge_amount"));
				map.put("recharge_status", rs.getString("recharge_status"));
				map.put("recharge_time", rs.getString("recharge_time"));
				map.put("recharge_amount", rs.getString("recharge_amount"));
				map.put("added_value_status", rs.getString("added_value_status"));
				map.put("replenish_id", rs.getString("replenish_id"));
				map.put("oilcard_phone", rs.getString("oilcard_phone"));
				map.put("payment_result_cord", rs.getString("payment_result_cord"));
				map.put("payment_time", rs.getString("payment_time"));
				map.put("order_create_time", rs.getString("order_create_time"));
				map.put("pay_order_id", rs.getString("pay_order_id"));
				map.put("oilcard_ordernum", rs.getString("oilcard_ordernum"));
				map.put("order_package_type", rs.getString("order_package_type"));
				map.put("oilcard_package_id", rs.getString("oilcard_package_id"));
				map.put("order_status", rs.getString("order_status"));
				map.put("id", rs.getString("id"));
				//////////////////////////////////
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}

	public List<Map<String, String>> getAllReOilCardRecharge(String oilOrderId, String chargeInfoId) {
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Map<String, String>> items = new ArrayList<Map<String, String>>();

		try {
			stmt = conn.createStatement();
			String sql = "SELECT yoo.*," +
					"(select member_mobile_phone from pay_member where member_openid = yoo.member_id) order_userid_phone" +
					" FROM yl_oilcard_order yoo "
					+ (isTest?"where yoo.id = 321":"");
			System.out.println("调用方法名getAllReOilCardRecharge的sql："+sql);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				Map<String, String> map = new HashMap<String, String>();
				map.put("payment_order_id", rs.getString("payment_order_id"));
				map.put("member_id", rs.getString("member_id"));
				map.put("oilcard_number", rs.getString("oilcard_number"));
				map.put("payment_type", rs.getString("payment_type"));
				map.put("payment_status", rs.getString("payment_status"));
				map.put("payment_amount", rs.getString("payment_amount"));
				map.put("recharge_amount", rs.getString("recharge_amount"));
				map.put("oilcard_owner", rs.getString("oilcard_owner"));
				map.put("oilcard_type", rs.getString("oilcard_type"));
				map.put("oilcard_phone", rs.getString("oilcard_phone"));
				map.put("payment_result_cord", rs.getString("payment_result_cord"));
				map.put("payment_time", rs.getString("payment_time"));
				map.put("order_create_time", rs.getString("order_create_time"));
				map.put("pay_order_id", rs.getString("pay_order_id"));
				map.put("oilcard_ordernum", rs.getString("oilcard_ordernum"));
				map.put("order_package_type", rs.getString("order_package_type"));
				map.put("oilcard_package_id", rs.getString("oilcard_package_id"));
				map.put("order_status", rs.getString("order_status"));
				map.put("id", rs.getString("id"));
				//////////////////////////////////
				map.put("order_userid_phone", rs.getString("order_userid_phone"));
				items.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			closeDataBase();
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return items;
	}


}
